﻿using fuzhu;
using Microsoft.AspNetCore.Rewrite;
using Microsoft.EntityFrameworkCore;
using SqlSuarEntity;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Net.NetworkInformation;
using System.Runtime.CompilerServices;
using System.Runtime.ConstrainedExecution;
using System.Text;
using System.Xml.Serialization;
using XXX.Common;
using XXX.Models;
using XXX.Models.U8Entity;
using XXX.Models.XXXEntities;
using ZDEntity;
using static XXX.Models.WDFT.WDFT_SaleOrderJinDu;
using static XXX.Models.WMS.ScanBarcode;

namespace XXX.Bo
{
    public class WMS_PanDianVM : WMS_PanDian
    {
        //当前的数据库连接字符串
        public string conStr { get; set; }
        public string cacc { get; set; }
        public string xmlConfig { get; set; }

        //账面条码数 Book
        public decimal? iBookCnt { get; set; }
        //实盘条码数
        public decimal? iChkCnt { get; set; }

        //账面数量 Book
        public decimal? iBookQty { get; set; }
        //实盘数量
        public decimal? iChkQty { get; set; }

        public string cPosName { get;set; }
        public string cInvName { get; set; }
        public string cWhName { get; set; }

        public bool bWhPos { get; set; }
        //账面数量
        public List<WMS_PanDians> bookList { get; set; }
        //盘点数量
        public List<WMS_PanDianBarcode> chkList { get; set; }

        public WMS_PanDianVM()
        {
            //conStr
            conStr = CanShu.conStr;
            cacc = CanShu.cZT;
            //初始化定义xmlconfig
            xmlConfig = XmlOperation.GetConfigXML();
        }

        //清理敏感信息
        public void clearConstr()
        {
            //conStr
            conStr = null;
            //初始化定义xmlconfig
            xmlConfig = null;
        }

        /// <summary>
        /// 只查询未执行完的盘点单
        /// </summary>
        /// <returns></returns>
        public RetModel2 Query()
        {
            using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = conStr,
                DbType = SqlSugar.DbType.SqlServer,
                IsAutoCloseConnection = true
            }))
            {
                var query = db.Queryable<WMS_PanDian>()
                              .Where(a => a.bClose == false && a.bDel== false);

                //盘点单号是空的，查询列表
                if (string.IsNullOrEmpty(cCheckCode))
                {
                    if (!string.IsNullOrEmpty(cWhCode))
                    {
                        query = query.Where(a => a.cWhCode == cWhCode);
                    }

                    if (!string.IsNullOrEmpty(cPosCode))
                    {
                        query = query.Where(a => a.cPosCode == cPosCode);
                    }

                    if (!string.IsNullOrEmpty(cInvCode))
                    {
                        query = query.Where(a => a.cInvCode == cInvCode);
                    }

                    if (dDate != null)
                    {
                        query = query.Where(a => a.dDate == dDate);
                    }
                }
                else
                {
                    query = query.Where(a => a.cCheckCode == cCheckCode);
                }
                

                // 选择需要的字段
                List<WMS_PanDianVM> lst = query.Select(a => new WMS_PanDianVM
                {
                    cWhCode = a.cWhCode,
                    cPosCode = a.cPosCode,
                    dDate = a.dDate,
                    cInvCode = a.cInvCode,
                    cCheckCode = a.cCheckCode,
                    iU8Qty = a.iU8Qty
                }).ToList();

              

                // 添加数量
                foreach (WMS_PanDianVM vm in lst)
                {
                    vm.clearConstr();
                    var bookquery = db.Queryable<WMS_PanDians>()
                                 .Where(it => it.cCheckCode == vm.cCheckCode);

                    vm.iBookQty = bookquery
                                 .Sum(it => it.iQty);
                    vm.iBookCnt = bookquery
                                .Count();
                    var chkquery = db.Queryable<WMS_PanDianBarcode>()
                                 .Where(it => it.cCheckCode == vm.cCheckCode);
                    vm.iChkQty = chkquery
                                 .Sum(it => it.iQty);
                    vm.iChkCnt = chkquery
                                .Count();

                    //盘点单号不为空，则查询详情
                    if (!string.IsNullOrEmpty(cCheckCode))
                    {
                        vm.bookList = db.Queryable<WMS_PanDians>().Where(x => x.cCheckCode == vm.cCheckCode).ToList();
                        vm.chkList = db.Queryable<WMS_PanDianBarcode>().Where(x => x.cCheckCode == vm.cCheckCode).ToList();
                        Warehouse wh = db.Queryable<Warehouse>().Where(x => x.cWhCode == vm.cWhCode).First();
                        vm.cWhName = wh.cWhName;
                        vm.bWhPos = wh.bWhPos;
                        if (!string.IsNullOrEmpty(vm.cInvCode))
                        {
                            vm.cInvName = db.Queryable<Inventory>().Where(x => x.cInvCode == vm.cInvCode).Select(x => x.cInvName).First();
                        }                       
                        
                        if (!string.IsNullOrEmpty(vm.cPosCode))
                        {
                            vm.cPosName = db.Queryable<Position>().Where(x => x.cPosCode == vm.cPosCode).Select(x => x.cPosName).First();
                        }
                    }

                }

                return DBService.ReOKObject(lst);

            }

        }

        /// <summary>
        /// 新增盘点单, 使用当前类新增
        /// </summary> 
        public RetModel2 Add()
        {
            try
            {
                //仓库不能为空
                if (string.IsNullOrWhiteSpace(cWhCode))
                {
                    return DBService.ReErrObject("仓库不能为空");
                }
                //取当前单据号，单据前缀
                string cQianZhui = "PD" + DateTime.Now.ToString("yyMM");
                //取最大单号
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {
                    // 查询满足条件的所有单据号
                    var maxBillNo = db.Queryable<WMS_PanDian>()
                                      .Where(x => x.cCheckCode.Substring(0, 6) == cQianZhui && x.cCheckCode.Length==9)
                                      .OrderByDescending(x => x.cCheckCode)
                                      .Select(x => x.cCheckCode)
                                      .First();

                    if (maxBillNo != null)
                    {
                        // 提取最后三位数字
                        int lastThreeDigits = int.Parse(maxBillNo.Substring(6));
                        // 最后两位数字加1
                        int newLastThreeDigits = lastThreeDigits + 1;
                        // 格式化为两位数
                        string formattedLastThreeDigits = newLastThreeDigits.ToString("D3");

                        // 生成新单据号
                        cCheckCode = $"{cQianZhui}{formattedLastThreeDigits}";
                    }
                    else
                    {
                        // 没有找到符合条件的单据号，生成初始单据号
                        cCheckCode = $"{cQianZhui}001";
                    }
                    //日期使用当前日期
                    dDate = DateTime.Now.Date;
                    bClose = false;
                    bDel = false;
                    dMakeTime = DateTime.Now;
                    iU8Qty = 0;
                    //如果货位不为空，取货位存量的数量
                    if (!string.IsNullOrEmpty(cPosCode))
                    {
                        var U8Query = db.Queryable<InvPosition>().Where(a => a.cWhCode == cWhCode && a.cPosCode==cPosCode);
                        if (!string.IsNullOrEmpty(cInvCode))
                        {
                            U8Query.Where(a=>a.cInvCode==cInvCode);
                        }
                        iU8Qty = U8Query.Sum(x => x.iQuantity);
                    }
                    else
                    {
                        iU8Qty = db.Queryable<CurrentStock>().Where(a => a.cWhCode == cWhCode).Sum(a => a.iQuantity);
                    }

                  
                    //使用事务
                    try
                    {

                        db.BeginTran();
                        
                        int ire = db.Insertable<WMS_PanDian>(this).ExecuteCommand();
                        if (ire > 0)
                        {
                            //增加U8账面数量
                            //db.Updateable<CurrentStock>()
                            //    .UpdateColumns(it => new { it.iQty, it.iBookQty })
                            //    .Where(it => it.cWhCode == cWhCode && it.cState == "在库")
                            //    .ExecuteCommand();
                            //插入明细
                            // 构建初始查询
                            var query = db.Queryable<WMS_BARCODE>().Where(a =>
                                              a.cWhCode == cWhCode &&
                                              a.cState == "在库");

                            // 动态添加条件,为空或者不为空，相等
                            if (!string.IsNullOrEmpty(cPosCode) )
                            {
                                query = query.Where(a =>  a.cPosCode == cPosCode);
                            }
                            if (!string.IsNullOrEmpty(cInvCode))
                            {
                                query = query.Where(a => a.cInvCode == cInvCode);
                            }

                            
                            List<WMS_PanDians> lst = query.Select(a => new WMS_PanDians()
                            {
                                cCheckCode = cCheckCode,
                                Barcodeno = a.Barcodeno,
                                cInvCode = a.cInvCode,
                                cBatch = a.cBatch,
                                cWhCode = a.cWhCode,
                                cPosCode = a.cPosCode,
                                cFree1 = a.cFree1,
                                cFree2 = a.cFree2,
                                cFree3 = a.cFree3,
                                cFree4 = a.cFree4,
                                cState = a.cState,
                                iQty = a.iQty,
                                bAdd = false, 
                                dMakeTime = DateTime.Now,
                            }).ToList();
                            db.Insertable<WMS_PanDians>(lst).ExecuteCommand();
                        }
                        db.CommitTran();
                    }
                    catch (Exception ex)
                    {
                        db.RollbackTran();
                        throw ex;
                    }
                    return DBService.ReOKObject(cCheckCode);
                }
            }
            catch (Exception ex)
            {
                return DBService.ReErrObject(ex.Message);
            }

        }


        /// <summary>
        /// 盘点单条码状态检查
        /// </summary>
        /// <returns></returns>
        public RetModel2 ChangeCheck()
        {
            try
            {
                //当前条码状态是否有更改，
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {

                    var invlst = db.Queryable<WMS_PanDians>()
                         .InnerJoin<WMS_BARCODE>((a, b) => a.Barcodeno == b.Barcodeno)
                            .Where((a, b) => a.cCheckCode == cCheckCode && a.cState != b.cState)
                            .Select((a, b) => a.cInvCode)
                            .Distinct().ToList();
                    if (invlst == null)
                    {
                        return DBService.ReOKObject("没有更改");
                    }
                    else
                    {
                        string cErrinv = "";
                        foreach (string cinvcode in invlst)
                        {
                            cErrinv += cinvcode + ";";
                        }
                        return DBService.ReErrObject("以下产品条码状态更改" + cErrinv);
                    }
                }

            }
            catch (Exception ex)
            {
                return DBService.ReErrObject(ex.Message);
            }

        }

        /// <summary>
        /// 盘点单对比，传入盘点号，返回booklist对比表
        /// </summary>
        /// <returns></returns>
        public RetModel2 PanDianDuiBi()
        {
            try
            {
                //当前条码状态是否有更改，
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {
                    WMS_PanDian pd = db.Queryable<WMS_PanDian>().Where(x => x.cCheckCode == cCheckCode).First();
                    //读取盘点单的仓库和货位
                    cWhCode = pd.cWhCode;
                    //cPosCode = pd.cPosCode;


                    bookList = db.Queryable<WMS_PanDians>().Where(x => x.cCheckCode == cCheckCode).ToList();
                    chkList = db.Queryable<WMS_PanDianBarcode>().Where(x => x.cCheckCode == cCheckCode).ToList();

                    List<WMS_PanDians> addlist = new List<WMS_PanDians>();

                    // 匹配并更新 bChecked
                    foreach (var barcode in chkList)
                    {
                        var matchingPandian = bookList.FirstOrDefault(p => p.Barcodeno == barcode.Barcodeno);
                        if (matchingPandian != null)
                        {
                            // 更新匹配的项
                            matchingPandian.bChecked = true;
                            matchingPandian.newPosCode = barcode.newPosCode;
                            matchingPandian.newState = "在库";
                        }
                        else
                        {
                            // 插入新的项
                            addlist.Add(new WMS_PanDians
                            {
                                Barcodeno = barcode.Barcodeno,
                                cCheckCode = barcode.cCheckCode,
                                cInvCode = barcode.cInvCode,
                                cBatch = barcode.cBatch,
                                cWhCode = barcode.cWhCode,
                                cPosCode = barcode.cPosCode,
                                cState = barcode.cState,
                                cFree1 = barcode.cFree1,
                                cFree2 = barcode.cFree2,
                                cFree3 = barcode.cFree3,
                                cFree4 = barcode.cFree4,
                                iQty = barcode.iQty,
                                bChecked = true,
                                bAdd = true,
                                dMakeTime = DateTime.Now,
                                newPosCode = barcode.newPosCode,
                                newState = "在库"

                            });
                        }
                    }

                    // 设置未匹配的项的 bChecked 为 false
                    foreach (var pandian in bookList)
                    {
                        if (pandian.bChecked == null)
                        {
                            pandian.bChecked = false; 
                            pandian.newPosCode = "";
                            pandian.newState = "盘亏出库";
                        }
                    }
                    try
                    {
                        db.BeginTran();
                        //更新数据库
                        db.Updateable<WMS_PanDians>(bookList)
                            .ExecuteCommand();
                        db.Insertable(addlist).ExecuteCommand();


                        db.CommitTran();
                    }
                    catch (Exception ex)
                    {
                        db.RollbackTran();
                        throw ex;
                    }

                    bookList = db.Queryable<WMS_PanDians>().Where(x => x.cCheckCode == cCheckCode).ToList();
                    return DBService.ReOKObject(bookList);
                }
            }
            catch (Exception ex)
            {
                return DBService.ReErrObject(ex.Message);
            }

        }

        /// <summary>
        /// 现存量对比
        /// </summary>
        /// <returns></returns>
        public RetModel<Rep_U8StockDuiBi> Rep_BarcodeU8StockDuiBi(Rep_U8StockDuiBi query)
        {
            try
            {
                //当前条码状态是否有更改，
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {
                    string sql = @"select  aa.*,i.cInvName,i.cInvStd from 
(select 
COALESCE(A.cWhCode, B.cWhCode)  as cWhCode,
 COALESCE(A.cInvCode, B.cInvCode)  as cInvCode,
 COALESCE(A.cBatch, B.cBatch)  as cBatch,
 COALESCE(A.cFree1, B.cFree1)  as cFree1,
 COALESCE(A.cFree2, B.cFree2)  as cFree2,
 COALESCE(A.cFree3, B.cFree3)  as cFree3,
 COALESCE(A.cFree4, B.cFree4)  as cFree4,
 a.iQuantity as iU8Qty, b.iQty ,(isnull(a.iQuantity,0)- isnull( b.iQty,0)) as chayi 
 from  CurrentStock a
full join 
(select cWhCode, cInvCode,cBatch,cFree1,cFree2,cFree3,cFree4,sum(iQty) iQty from WMS_BARCODE
where cState='在库'
GROUP BY cWhCode, cInvCode,cBatch,cFree1,cFree2,cFree3,cFree4) b 
on a.cWhCode = b.cWhCode AND a.cInvCode = b.cInvCode and ISNULL(a.cBatch,'')= ISNULL(b.cBatch,'')
and ISNULL(a.cFree1,'') = ISNULL(b.cFree1,'')
and ISNULL(a.cFree2,'') = ISNULL(b.cFree2,'')  
and ISNULL(a.cFree3,'') = ISNULL(b.cFree3,'')  
and ISNULL(a.cFree4,'') = ISNULL(b.cFree4,'') ) aa
inner join Inventory i on aa.cInvCode = i.cinvcode where 1=1   ";


                    //cWhCode,cInvCode,cBatch ,cFree1,cFree2,cFree3,cFree4 设置查询条件
                    if (!string.IsNullOrEmpty(query.cWhCode))
                    {
                        sql += string.Format(" and aa.cWhCode='{0}'", query.cWhCode);
                    }
                    if (!string.IsNullOrEmpty(query.cInvCode))
                    {
                        sql += string.Format(" and aa.cInvCode='{0}'", query.cInvCode);
                    }
                    if (!string.IsNullOrEmpty(query.cBatch))
                    {
                        sql += string.Format(" and aa.cBatch='{0}'", query.cBatch);
                    }
                    if (!string.IsNullOrEmpty(query.cFree1))
                    {
                        sql += string.Format(" and aa.cFree1='{0}'", query.cFree1);
                    }
                    if (!string.IsNullOrEmpty(query.cFree2))
                    {
                        sql += string.Format(" and aa.cFree2='{0}'", query.cFree2);
                    }
                    if (!string.IsNullOrEmpty(query.cFree3))
                    {
                        sql += string.Format(" and aa.cFree3='{0}'", query.cFree3);
                    }
                    if (!string.IsNullOrEmpty(query.cFree4))
                    {
                        sql += string.Format(" and aa.cFree4='{0}'", query.cFree4);
                    }

                    List<Rep_U8StockDuiBi> lst = db.SqlQueryable<Rep_U8StockDuiBi>(sql).ToList();
                    return DBService.ReOK<Rep_U8StockDuiBi>(lst);
                }
            }
            catch (Exception ex)
            {
                return DBService.ReError<Rep_U8StockDuiBi>(ex.Message);
            }

        }

        /// <summary>
        /// 现存量对比
        /// </summary>
        /// <returns></returns>
        public RetModel<Rep_U8StockDuiBi> Rep_BarcodeU8PosStockDuiBi(Rep_U8StockDuiBi query)
        {
            try
            {
                //当前条码状态是否有更改，
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {
                    string sql = @"select  aa.*,i.cInvName,i.cInvStd from 
(select 
COALESCE(A.cWhCode, B.cWhCode)  as cWhCode,
COALESCE(A.cPosCode, B.cPosCode)  as cPosCode,
 COALESCE(A.cInvCode, B.cInvCode)  as cInvCode,
 COALESCE(A.cBatch, B.cBatch)  as cBatch,
 COALESCE(A.cFree1, B.cFree1)  as cFree1,
 COALESCE(A.cFree2, B.cFree2)  as cFree2,
 COALESCE(A.cFree3, B.cFree3)  as cFree3,
 COALESCE(A.cFree4, B.cFree4)  as cFree4,
 a.iQuantity as iU8Qty, b.iQty ,(isnull(a.iQuantity,0)- isnull( b.iQty,0)) as chayi 
 from  InvPositionSum a
full join 
(select cWhCode,cPosCode, cInvCode,cBatch,cFree1,cFree2,cFree3,cFree4,sum(iQty) iQty from WMS_BARCODE
where cState='在库'
GROUP BY cWhCode,cPosCode, cInvCode,cBatch,cFree1,cFree2,cFree3,cFree4) b 
on a.cWhCode = b.cWhCode 
AND a.cInvCode = b.cInvCode
AND a.cPosCode = b.cPosCode
 and ISNULL(a.cBatch,'')= ISNULL(b.cBatch,'')
and ISNULL(a.cFree1,'') = ISNULL(b.cFree1,'')
and ISNULL(a.cFree2,'') = ISNULL(b.cFree2,'')  
and ISNULL(a.cFree3,'') = ISNULL(b.cFree3,'')  
and ISNULL(a.cFree4,'') = ISNULL(b.cFree4,'') ) aa
inner join Inventory i on aa.cInvCode = i.cinvcode where 1=1    ";


                    //cWhCode,cInvCode,cBatch ,cFree1,cFree2,cFree3,cFree4 设置查询条件
                    if (!string.IsNullOrEmpty(query.cWhCode))
                    {
                        sql += string.Format(" and aa.cWhCode='{0}'", query.cWhCode);
                    }
                    if (!string.IsNullOrEmpty(query.cPosCode))
                    {
                        sql += string.Format(" and aa.cPosCode='{0}'", query.cPosCode);
                    }
                    if (!string.IsNullOrEmpty(query.cInvCode))
                    {
                        sql += string.Format(" and aa.cInvCode='{0}'", query.cInvCode);
                    }
                    if (!string.IsNullOrEmpty(query.cBatch))
                    {
                        sql += string.Format(" and aa.cBatch='{0}'", query.cBatch);
                    }
                    if (!string.IsNullOrEmpty(query.cFree1))
                    {
                        sql += string.Format(" and aa.cFree1='{0}'", query.cFree1);
                    }
                    if (!string.IsNullOrEmpty(query.cFree2))
                    {
                        sql += string.Format(" and aa.cFree2='{0}'", query.cFree2);
                    }
                    if (!string.IsNullOrEmpty(query.cFree3))
                    {
                        sql += string.Format(" and aa.cFree3='{0}'", query.cFree3);
                    }
                    if (!string.IsNullOrEmpty(query.cFree4))
                    {
                        sql += string.Format(" and aa.cFree4='{0}'", query.cFree4);
                    }

                    List<Rep_U8StockDuiBi> lst = db.SqlQueryable<Rep_U8StockDuiBi>(sql).ToList();
                    return DBService.ReOK<Rep_U8StockDuiBi>(lst);
                }
            }
            catch (Exception ex)
            {
                return DBService.ReError<Rep_U8StockDuiBi>(ex.Message);
            }

        }


        /// <summary>
        /// 现存量对比
        /// </summary>
        /// <returns></returns>
        public RetModel<Rep_U8CheckVouchDuiBi> Rep_BarcodeU8PanDianDuiBi(Rep_U8CheckVouchDuiBi query)
        {
            try
            {
                //当前条码状态是否有更改，
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {
                    string sql = $@"select  aa.*,i.cInvName,i.cInvStd from 
(select 
COALESCE(A.cWhCode, B.cWhCode)  as cWhCode,
COALESCE(a1.cPosition, B.cPosCode)  as cPosCode,
 COALESCE(a1.cInvCode, B.cInvCode)  as cInvCode,
 COALESCE(a1.cCVBatch, B.cBatch)  as cBatch,
 COALESCE(a1.cFree1, B.cFree1)  as cFree1,
 COALESCE(a1.cFree2, B.cFree2)  as cFree2,
 COALESCE(a1.cFree3, B.cFree3)  as cFree3,
 COALESCE(a1.cFree4, B.cFree4)  as cFree4,
 a1.iCVQuantity as iU8Qty, b.iQty ,(isnull(a1.iCVQuantity,0)- isnull( b.iQty,0)) as chayi 
 from  CheckVouch a
 inner join CheckVouchs a1 on a.id = a1.id  and a.cCVCode='{query.cCheckCode}'
full join 
(select b.cWhCode,b.cPosCode, b.cInvCode,b.cBatch,b.cFree1,b.cFree2,b.cFree3,b.cFree4,sum(iQty) iQty 
from   WMS_PanDianBarcode b  
where cState='在库' and b.cCheckCode = '{query.PDCode}'
GROUP BY b.cWhCode,b.cPosCode, b.cInvCode,b.cBatch,b.cFree1,b.cFree2,b.cFree3,b.cFree4) b 
on a.cWhCode = b.cWhCode 
AND a1.cInvCode = b.cInvCode
AND a1.cPosition = b.cPosCode
 and ISNULL(a1.cCVBatch,'')= ISNULL(b.cBatch,'')
and ISNULL(a1.cFree1,'') = ISNULL(b.cFree1,'')
and ISNULL(a1.cFree2,'') = ISNULL(b.cFree2,'')  
and ISNULL(a1.cFree3,'') = ISNULL(b.cFree3,'')  
and ISNULL(a1.cFree4,'') = ISNULL(b.cFree4,'') ) aa
inner join Inventory i on aa.cInvCode = i.cinvcode where 1=1   ";


                    //cWhCode,cInvCode,cBatch ,cFree1,cFree2,cFree3,cFree4 设置查询条件
                    if (!string.IsNullOrEmpty(query.cWhCode))
                    {
                        sql += string.Format(" and aa.cWhCode='{0}'", query.cWhCode);
                    }
                    if (!string.IsNullOrEmpty(query.cInvCode))
                    {
                        sql += string.Format(" and aa.cInvCode='{0}'", query.cInvCode);
                    }
                    if (!string.IsNullOrEmpty(query.cBatch))
                    {
                        sql += string.Format(" and aa.cBatch='{0}'", query.cBatch);
                    }
                    if (!string.IsNullOrEmpty(query.cFree1))
                    {
                        sql += string.Format(" and aa.cFree1='{0}'", query.cFree1);
                    }
                    if (!string.IsNullOrEmpty(query.cFree2))
                    {
                        sql += string.Format(" and aa.cFree2='{0}'", query.cFree2);
                    }
                    if (!string.IsNullOrEmpty(query.cFree3))
                    {
                        sql += string.Format(" and aa.cFree3='{0}'", query.cFree3);
                    }
                    if (!string.IsNullOrEmpty(query.cFree4))
                    {
                        sql += string.Format(" and aa.cFree4='{0}'", query.cFree4);
                    }

                    List<Rep_U8CheckVouchDuiBi> lst = db.SqlQueryable<Rep_U8CheckVouchDuiBi>(sql).ToList();
                    return DBService.ReOK<Rep_U8CheckVouchDuiBi>(lst);
                }
            }
            catch (Exception ex)
            {
                return DBService.ReError<Rep_U8CheckVouchDuiBi>(ex.Message);
            }

        }

        /// <summary>
        /// 盘点单扫描更新
        /// </summary>
        /// <returns></returns>
        public async Task<RetModel2> PanDianUpdate()
        {
            
                //当前条码状态是否有更改，
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {
                    //读取当前盘点单
                    WMS_PanDian pd = db.Queryable<WMS_PanDian>().Where(x => x.cCheckCode == cCheckCode).First();
                    //读取盘点单的仓库和货位
                    cWhCode = pd.cWhCode;
                //已经关闭的盘点单不能再更新状态
                //盘点单状态： 已经更新条码状态后，盘点单自动关闭。
                //已经关闭的盘点单，pda中不再显示，不能再次扫描。 也不可以再次更新盘点单
                if (pd.bClose==true)
                {
                    return DBService.ReOKObject("当前盘点单已关闭");

                }
                    //构建更新的wmsbarcode，只更新 新增条码，或者 货位和之前不一样，或者状态不一样的 
                    List<WMS_PanDians> lstPosChange = db.Queryable<WMS_PanDians>().Where(x => x.cCheckCode == cCheckCode &&  (x.bAdd==true || (SqlFunc.IsNullOrEmpty(x.cPosCode) ? "" : x.cPosCode)
            != (SqlFunc.IsNullOrEmpty(x.newPosCode) ? "" : x.newPosCode) || (SqlFunc.IsNullOrEmpty(x.cState) ? "" : x.cState) != (SqlFunc.IsNullOrEmpty(x.newState) ? "" : x.newState)))
                       .ToList();
                   
                    try
                    {
                        db.BeginTran();

                        //实体更新，需要使用UpdateColumns
                        // 首先设置要更新的字段值
                        pd.bClose = true;

                        // 然后执行更新，指定根据哪个字段匹配。关闭盘点单
                        db.Updateable(pd)
                           .UpdateColumns(it => it.bClose)  // 只更新bClose字段
                           .WhereColumns(it => it.cCheckCode)  // 根据cCheckCode匹配
                           .ExecuteCommand();
                        foreach(WMS_PanDians item in lstPosChange)
                        {
                            WMS_BARCODE wMS_BARCODE = db.Queryable<WMS_BARCODE>().Where(x => x.Barcodeno == item.Barcodeno).First();


                            WMS_BARCODE_LOG log = new WMS_BARCODE_LOG();
                            //新增条码，代表之前仓库可能不一样
                            if (item.bAdd==true)
                            { 
                                log.cBarcodeno = item.Barcodeno;
                                log.cUserName = pd.cMaker;
                                log.dMakeTime = DateTime.Now;
                                log.cCode = cCheckCode;
                                log.cSourceId = item.autoid.ToString();
                                log.cVouchType = "盘点单";
                                log.cRemark = "盘盈入库";
                                log.cWhCode = cWhCode;
                                log.cWhCodeBefore = wMS_BARCODE.cWhCode;
                                log.cPosCode = item.newPosCode;
                                log.cPosCodeBefore = wMS_BARCODE.cPosCode;
                                log.iQuantityBefore = wMS_BARCODE.iQty;
                                log.iQuantity = wMS_BARCODE.iQty;
                                log.cStateBefore = item.newState;
                                log.cState = item.cState;

                                wMS_BARCODE.cWhCode = cWhCode;
                                wMS_BARCODE.cPosCode = item.newPosCode;
                                wMS_BARCODE.cState = item.newState;
                                wMS_BARCODE.cInType = "盘点入库";
                                wMS_BARCODE.cInCode = cCheckCode;
                                wMS_BARCODE.RKDate = pd.dDate;
                            }
                            else if (item.bChecked==false)
                            {
                                //bchecked = 0，代表没盘点到，判断出库
                                log.cBarcodeno = item.Barcodeno;
                                log.cUserName = pd.cMaker;
                                log.dMakeTime = DateTime.Now;
                                log.cCode = cCheckCode;
                                log.cSourceId = item.autoid.ToString();
                                log.cVouchType = "盘点单";
                                log.cRemark = "盘亏出库";
                                log.cWhCode = "";
                                log.cWhCodeBefore = wMS_BARCODE.cWhCode;
                                log.cPosCode = "";
                                log.cPosCodeBefore = wMS_BARCODE.cPosCode;
                                log.iQuantityBefore = wMS_BARCODE.iQty;
                                log.iQuantity = wMS_BARCODE.iQty;
                                log.cStateBefore = item.newState;
                                log.cState = item.cState;

                                wMS_BARCODE.cWhCode = cWhCode;
                                wMS_BARCODE.cPosCode = wMS_BARCODE.cPosCode;
                                wMS_BARCODE.cState = item.newState;
                                wMS_BARCODE.cOutType = "盘亏出库";
                                wMS_BARCODE.cOutCode = cCheckCode;
                                wMS_BARCODE.CKDate = pd.dDate;

                            }
                          else
                            {
                                //货位调整的
                                log.cBarcodeno = item.Barcodeno;
                                log.cUserName = pd.cMaker;
                                log.dMakeTime = DateTime.Now;
                                log.cCode = cCheckCode;
                                log.cSourceId = item.autoid.ToString();
                                log.cVouchType = "盘点单";
                                log.cRemark = "货位调整";
                                log.cWhCode = cWhCode;
                                log.cWhCodeBefore = wMS_BARCODE.cWhCode;
                                log.cPosCode = item.newPosCode;
                                log.cPosCodeBefore = wMS_BARCODE.cPosCode;
                                log.iQuantityBefore = wMS_BARCODE.iQty;
                                log.iQuantity = wMS_BARCODE.iQty;
                                log.cStateBefore = item.newState;
                                log.cState = item.cState;

                                wMS_BARCODE.cWhCode = cWhCode;
                                wMS_BARCODE.cPosCode = wMS_BARCODE.cPosCode;
                                wMS_BARCODE.cState = item.newState;
                            }

                            db.Updateable(wMS_BARCODE).ExecuteCommand();
                            db.Insertable(log).ExecuteCommand();


                        }
                       
                        //查询之前的标签状态


                        db.CommitTran();
                    }
                    catch (Exception ex)
                    {
                        db.RollbackTran();
                    return DBService.ReErrObject(ex.Message);
                }




                    return DBService.ReOKObject("更改完成");
                }
            

        }
        /// <summary>
        /// 删除盘点单
        /// </summary>
        /// <returns></returns>
        public RetModel2 DelVouch()
        {
            try
            {
                //更新bdel = 1
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {

                    //表达式更新，没有实体。 相当于根据条件更新表
                    db.Updateable<WMS_PanDian>()
                            .SetColumns(it => new WMS_PanDian
                            {
                                bDel = true
                            })
                            .Where(it => it.cCheckCode == cCheckCode)
                            .ExecuteCommand();
                }



                return DBService.ReOKObject("删除完成");
            }
            catch (Exception ex)
            {
                return DBService.ReErrObject(ex.Message);
            }

        }


        /// <summary>
        /// 删除盘点单的条码
        /// </summary>
        /// <returns></returns>
        public RetModel2 DelBarcode(string cCheckCode,string Barcodeno)
        {
            try
            {
                //更新bdel = 1
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {
                    db.Deleteable<WMS_PanDianBarcode>()
                            .Where(it => it.cCheckCode == cCheckCode &&it.Barcodeno==Barcodeno)
                            .ExecuteCommand();
                }

                return DBService.ReOKObject("删除完成");


            }
            catch (Exception ex)
            {
                return DBService.ReErrObject(ex.Message);
            }

        }

        /// <summary>
        /// 删除盘点单的条码
        /// </summary>
        /// <returns></returns>
        public RetModel2 DelBarcodeAll(string _cCheckCode)
        {
            try
            {
                //更新bdel = 1
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {
                    db.Deleteable<WMS_PanDianBarcode>()
                            .Where(it => it.cCheckCode == _cCheckCode)
                            .ExecuteCommand();
                }

                return DBService.ReOKObject("删除完成");


            }
            catch (Exception ex)
            {
                return DBService.ReErrObject(ex.Message);
            }

        }

        /// <summary>
        /// 保存扫描的盘点条码
        /// </summary>
        /// <returns></returns>
        public RetModel2 SaveChkBarcode()
        {
            try
            {
                //更新bdel = 1
                using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = conStr,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }))
                {
                    //当前没有的才插入进去
                  List<WMS_PanDianBarcode>  nowList = db.Queryable<WMS_PanDianBarcode>().Where(x => x.cCheckCode == cCheckCode).ToList();
                    // 找出 chkList 中不在 nowList 中的数据
                    List<WMS_PanDianBarcode> newList = chkList.Where(chkItem =>
                        !nowList.Any(nowItem => nowItem.Barcodeno == chkItem.Barcodeno)).ToList();

                    if (newList.Count > 0)
                    {
                        db.Insertable(newList).ExecuteCommand();
                        Console.WriteLine($"{newList.Count} 新记录已插入数据库。");
                    }

                }

                return DBService.ReOKObject("保存完成");


            }
            catch (Exception ex)
            {
                return DBService.ReErrObject(ex.Message);
            }

        }
    }
}


     

    


     

     

    
     
